import json

import xlwt

from quote_folder.Db_connect import DB_connect


class AAA():
    def __init__(self):
        self.develop_con = DB_connect().online_db()  # 开发数据库连接
        self.develop_cur = self.develop_con.cursor()
        self.bigdata_con = DB_connect().online_big_data()  # 数据组数据库连接
        self.bigdata_cur = self.bigdata_con.cursor()
        self.info_list = []

    def get_data(self):
        sql = """SELECT application_number, GROUP_CONCAT(DISTINCT money_type SEPARATOR '+') as money_type,sum(money) as money,close_date, pay_status FROM 
annual_fee_information_0321 WHERE DATE_SUB(CURDATE(), INTERVAL -30 DAY) >= date(close_date) AND close_date>=NOW() GROUP BY application_number,close_date,pay_status ORDER BY close_date DESC """
        self.bigdata_cur.execute(sql)
        returndata = self.bigdata_cur.fetchall()
        for i in returndata:
            if self.deal_sqh(i[0], i[1]):
                continue
            item = {}
            item = self.is_stop(i[0], item)
            item['sqh'] = i[0]
            item = self.get_marks(item['sqh'], item)
            item['money_type'] = i[1]
            item['money'] = i[2]
            item['close_date'] = i[3]
            item['state'] = i[4]
            self.get_lianxiren(item)

        self.save_json_dict(self.info_list)

    def is_stop(self, sqh, item):
        sql = f"SELECT name, is_stop FROM `mykhadmin`.`finals` WHERE `application_number` = 'ZL{sqh}'"
        self.develop_cur.execute(sql)
        data = self.develop_cur.fetchall()
        item['patent_name'] = data[0][0]
        item['is_stop'] = data[0][1]
        return item

    def get_marks(self, sqh, item):
        sql = f"SELECT marks FROM `big_data`.`MoneyWarning` WHERE `sqh` = '{sqh}'"
        self.bigdata_cur.execute(sql)
        data = self.bigdata_cur.fetchall()
        if data and data[0][0]:
            marks = data[0][0]
            if "放弃" in marks:
                item['marks'] = '放弃'
                item['time'] = ""
            else:
                item['marks'] = json.loads(marks)[0]["marks"]
                item['time'] = json.loads(marks)[0]['insert_time']
        else:
            item['marks'] = ""
            item['time'] = ""
        return item

    def get_lianxiren(self, item):
        sqh = item['sqh']
        sql = f"""SELECT unique_number FROM `mykhadmin`.`finals` WHERE `application_number` = 'ZL{sqh}'"""
        self.develop_cur.execute(sql)
        data = self.develop_cur.fetchall()
        self.get_lianxir_info(data[0][0], item)

    def get_lianxir_info(self, order_num, item):
        sql = f"""SELECT patent_user, liaisons, liaisons_tel FROM `mykhadmin`.`orders` WHERE `order_number` = '{order_num}'"""
        self.develop_cur.execute(sql)
        data = self.develop_cur.fetchall()
        item['patent_user'] = data[0][0]
        item['lianxr'] = data[0][1]
        item['tel'] = data[0][2]
        self.info_list.append(item)

    def save_json_dict(self, info_list):
        workbook = xlwt.Workbook()
        sheet = workbook.add_sheet("sheet1")
        sheet.write(0, 0, '序号')
        sheet.write(0, 1, '申请号')
        sheet.write(0, 2, '专利名字')
        sheet.write(0, 3, '专利权人')
        sheet.write(0, 4, '费用类型')
        sheet.write(0, 5, '金额')
        sheet.write(0, 6, '截止日')
        sheet.write(0, 7, '状态')
        sheet.write(0, 8, '联系人')
        sheet.write(0, 9, '电话')
        sheet.write(0, 10, "备注")
        sheet.write(0, 11, 'is_stop')
        sheet.write(0, 12, "备注时间")
        if len(info_list) > 0:
            for i in range(len(info_list)):
                sheet.write(i + 1, 0, i + 1)
                sheet.write(i + 1, 1, info_list[i]['sqh'])
                sheet.write(i + 1, 2, info_list[i]['patent_name'])
                sheet.write(i + 1, 3, info_list[i]['patent_user'])
                sheet.write(i + 1, 4, info_list[i]['money_type'])
                sheet.write(i + 1, 5, info_list[i]['money'])
                sheet.write(i + 1, 6, info_list[i]['close_date'])
                sheet.write(i + 1, 7, info_list[i]['state'])
                sheet.write(i + 1, 8, info_list[i]['lianxr'])
                sheet.write(i + 1, 9, info_list[i]['tel'])
                sheet.write(i + 1, 10, info_list[i]['marks'])
                sheet.write(i + 1, 11, info_list[i]['is_stop'])
                sheet.write(i + 1, 12, info_list[i]['time'])
            workbook.save('./年费催缴.xls')

    def deal_sqh(self, sqh, money_type):
        sql = f"""SELECT * FROM `big_data`.`ccy_paid` WHERE `application_number` = '{sqh}' AND `money_type` = '{money_type}'"""
        self.bigdata_cur.execute(sql)
        aa = self.bigdata_cur.fetchall()
        if aa:
            return True
        else:
            return False


if __name__ == '__main__':
    AAA().get_data()
